Intermediate PHP and MySQL

Introduction

Download the code for this lesson.

Okay, now that you've got the meat and potatoes portion of the Food Store application working (no pun intended), it's time to work on a few of the frills in the application. Today, we'll walk through some more advanced topics that add a little bit of spice to things (okay, maybe that pun was intended).

First, you'll examine searching. Just about every Web site offers some type of searching capability. If you want your store to look professional, you'll need to do that as well. However, in keeping with our more advanced topics theme, you'll be implementing a more advanced method of searching. You won't just be looking for words in your product catalog. You'll allow our customers to do some pretty advanced searching for your products using a well-known pattern-matching method supported in MySQL.

After that, we'll turn our attention back to the store manager, who has to wade through a ton of data to keep the store running smoothly. You'll try to make his or her life easier by providing a few fancy reports. One way to do that is to produce reports with as much information as possible. Producing reports with vague product or customer codes won't help any. You'll learn how to generate reports using multiple tables in the database, and how to link tables together to provide lots of information for the manager.

Finally, we'll get really fancy with our reports. So far, we've produced data for Web pages. Now we'll look at producing data for spreadsheets, allowing the manager to export data easily from the store application.

That should keep our brains busy for the day. Let's move on to Chapter 2 and look at searching.

Searching

Searching has become one of the hottest topics in the Web world. Thanks to sites such as Google and Yahoo!, Web users have come to expect some pretty advanced features in the searching abilities of Web sites. In order to provide a professional environment for your shoppers, you need to provide some form of searching.

Most store sites allow their customers to search through the catalog of products to filter only the items they're interested in viewing. To do that, you must provide a textbox for them to enter a search string. We've already done that in the Food Store navigation bar area. Now we just need to produce the PHP code for implementing the searching.

The search textbox on the main Web page

Searching for Data in MySQL

The MySQL database engine provides multiple ways for you to search data fields for values. You've already seen the WHERE clause in action. This feature allows you to display only records where a data field is equal to a specific value. This is the most basic form of searching for data. However, this doesn't allow you to search for a string contained within the total data field.

Instead of looking for a specific value in a data field, you'll want to produce data fields that are similar to the search string. For example, if the customer enters the word juice in the search textbox, you'll want to return records where the product description contains the word juice anywhere in the data field, not just the single word juice.

This is called pattern matching. You provide a pattern, which the MySQL database engine then matches against all of the records in a table. If a record matches the pattern, it's returned in the result set.

When you're searching for data patterns within data fields, MySQL provides two methods for matching data:

• SQL pattern matching
• Regular expression pattern matching

In SQL pattern matching, MySQL uses the standard SQL LIKE clause format. The LIKE clause allows you to combine characters with wildcard characters to create a pattern. This method uses an underscore to match a single character in the pattern and a percent symbol to match zero or more characters in the pattern. As a result, the query

matches any data record that contains the word juice, preceded by any number of characters (including none). This would return records containing apple juice, grape juice, or just juice.

If you want to match records that contain the word juice anywhere in the data field, you can use two wildcard characters:

This would return records like apple juice concentrate.

In regular expression pattern matching, your customer provides you with a coded pattern used to search for text. Regular expressions use their own language to define how to match data. The regular expression language specifies exactly how to filter data results using tons of search characters. It's amazing how detailed you can get using regular expressions.

MySQL supports regular expression queries using the REGEXP clause. You specify the matching pattern similar to how you did with the LIKE clause:

In a regular expression, by default, any text you enter is matched anywhere in the data field. Thus, this query automatically returns any data record that contains the word juice anywhere in the description. No wildcard characters are required!

You can define exactly where in the string the text pattern should match by using two special characters. The caret character (^) indicates that the data pattern must be at the start of a string. Therefore, the query

only returns records where the description starts with the text App (such as Apple juice). Similarly, the dollar sign ($) indicates the data must be at the end of a string. Consequently the query

only returns records that end with the word juice. Thus, the data field value apple juice concentrate wouldn't match, but apple juice would.

The MySQL regular expression language uses lots of special characters to define the search. If you've ever used regular expressions in programming languages such as Perl or Python, you'll notice that MySQL uses many of the same characters. You'll find some of the more popular ones below:

Another example of a regular expression would be:

This simple query returns any record that contains either the word apple or grape anywhere in the data field. You can start to see the power of simple regular expressions. Trying to create that search using the LIKE clause would be somewhat complicated.

Coding the Search

The search.inc.php file handles the searching for the Food Store application. Go ahead and create that file. Then we'll talk about how it works. Here are the steps for creating the file:

1. Create the file search.inc.php in the store folder under the application area.
2. Open the file with a text editor and add the following code:

   Print code
   

   <?php
      $search = $_GET['searchFor'];
      if (get_magic_quotes_gpc())
      {
         $search = stripslashes($search);
      }
      $searchsql = mysql_real_escape_string($search);
      echo "<h2>Results of searching '$search'<br><br></h2>\n";
      $query = "SELECT * from products WHERE description REGEXP '$searchsql'";
      $result = mysql_query($query);
      if (!$result)
      {
         echo "<h2>Sorry, unable to process search string.</h2>\n";
      } else
      {
         echo "<table width=\"100%\" border=\"0\">\n";
         while($row=mysql_fetch_array($result, MYSQL_ASSOC))
         {
            $prodid = $row['prodid'];
            $description = $row['description'];
            $price = $row['price'];
            $quantity = $row['quantity'];
            echo "<tr><td>\n";
               echo "<img src=\"showimage.php?id=$prodid\" width=\"80\" height=\"60\">";
            echo "</td><td>\n";
               if ($quantity == 0)
                  echo "<font size=\"3\">$description</font>\n";
               else
               {
                  echo "<a href=\"index.php?&content=updatecart&id=$prodid\">";
                  echo "<font size=\"3\"><b><u>$description</u></b></font></a>\n";
               }
            echo "</td><td>\n";
               printf("$%.2f\n", $price);
            echo "</td><td>\n";
               if ($quantity == 0)
                  echo "<font color=\"red\">Sorry, this item out of stock</font>\n";
               else if ($quantity < 5)
                  echo "Hurry, only $quantity left in stock!\n";
               else
                  echo " \n";
            echo "</td></tr>\n";
         }
         echo "</table>\n";
      }
   ?>

3. Save the file, and exit the text editor.

The search code retrieves the text the customer enters into the search textbox on the main Web page, uses our common method of scrubbing data for MySQL, and then sends it to the SELECT query using the standard REGEXP clause.

If the query returns any data, the search.inc.php code builds a table to display the results using links to allow the customer to view the product details and add it to the shopping cart.

The results of a simple search

This search technique allows our customers to perform searches for specific items, just like in some of the more advanced search tools. That's pretty fancy for a small amount of coding!

That completes the code for our Food Store storefront application. You've covered a lot of ground with this project! Let's move on to Chapter 3 and see what we can do for the manager in the back-end application.

Generating Reports

One thing you can't get away from, even in a paperless society, is reports. There's always some report you'll need to generate from the data in your database. The trick is doing this as quickly and as painlessly as possible.

If you use codes to identify data uniquely in your tables, normally, you'll need to generate reports that include data from more than one table. If you want to produce a report showing what products are in what categories, you'll want to display the actual category name and not the category ID value that's stored in the products table. This requires creating more complex SQL queries than what we've been doing so far in the project.

When you need to retrieve data from more than one table, you need a linking data field. The linking data field links a record in one table to a specific record in another table. An example of this is the catid data field in the products table. If you want to display the full category name in a product report, you must first retrieve the catid value from the product record in the products table and then look up that value in the categories table for the associated category name.

You can do this in two separate SELECT queries, one to retrieve the products record, then another to retrieve the matching categories record, but there's an even easier way. In SQL, you can query data from multiple tables at the same time!

When you retrieve data from multiple tables, you must list all of the tables in your FROM clause, then reference the individual data fields relative to the table they come from. Here's an example of what I'm talking about:

This query returns all of the products showing the product description and the full category name for the category they belong. Notice that every place where I reference a data field, I added the table name as well so the MySQL server knows which data field came from which table.

This format can get tedious, especially if you use long table and data field names. To help, SQL allows you to declare aliases. An alias is a shortcut name you can assign to a table name in a query. You can use the shortcut name anywhere in the SQL statement, even before you actually define it.

To define a shortcut, you use the AS clause within the FROM clause that specifies the tables. Here's an example of how that works:

The FROM clause defines the alias t1 to represent the products table name, and the alias t2 to represent the categories table name. The aliases are then used everywhere in the SQL statement where the tables are referenced.

Creating Food Store Reports

The Food Store administration back-end application allows you to generate three different reports for the store manager:

• A list of pending orders.
• The customer and product information for a specific order.
• A spreadsheet of products purchased during a specified time period.

You'll need to use a few complex SQL queries to obtain the information necessary to generate these reports. Let's look at creating these reports one at a time.

Listing Pending Orders

As the application records a new order in the orders table, it assigns the order a default status value of pending. This means that the customer placed the order, but the store manager hasn't processed it. The Food Store back-end application provides a link in the navigation area for the manager to list pending orders. The link passes the manager to the process.inc.php file. The file builds a list of pending orders, allowing the manager to select one order for processing. Let's create that file now.

1. Create a file called process.inc.php in the admin folder under the store folder in your application area.
2. Open the file in a text editor, and add the following code:

Print code


<?php
   echo "<h2>Pending Orders</h2><br>\n";
   $query = "SELECT t1.orderid, t1.custid, t1.date, t2.lastname";
   $query = $query . " FROM orders as t1, customers as t2";
   $query = $query . " WHERE t1.status = 'pending' AND t1.custid = t2.custid";
   $query = $query . "  ORDER BY t1.date";
   $result = mysql_query($query);
   echo "<table width=\"100%\" cellpadding=\"1\" border=\"1\">\n";
   echo "<tr><td>Order ID</td><td>Customer ID</td><td>Last Name</td><td>Date Submitted</td><td> </td></tr>\n";
   while($row=mysql_fetch_array($result, MYSQL_ASSOC))
   {
      $orderid = $row['orderid'];
      $custid = $row['custid'];
      $lastname = $row['lastname'];
      $date = $row['date'];
      echo "<tr><td>$orderid</td><td>$custid</td><td>$lastname</td><td>$date</td>\n";
      echo "<td><a href=\"admin.php?content=shiporder&id=$orderid\">Process</a></td></tr>\n";
   }
   echo "</table>\n";
?>

3. Save the file, and exit the editor.

You should be able to follow the complex SQL query now. It retrieves all of the orders records that have the status data field set to pending. It also displays the customer's last name by matching the custid data field in the orders table to the custid data field in the customers table. This adds a little personal touch to the report.

Processing an Order

When the manager selects a pending order from the list, the application continues to the shiporder.inc.php file, passing the orderid value. This code produces a complete bill, showing the customer's full information (including a shipping address), all of the products included in the order, and the total cost of the order. You know this report will require some fancy SQL!

Let's build that file now.

1. Create a file called shiporder.inc.php in the admin folder under the store folder in your application area.
2. Open the file in a text editor, and add the following code>

Print code


<?php
   $orderid = $_GET['id'];
   $query = "SELECT t1.orderid, t1.custid, t1.date,";
   $query = $query . " t2.lastname, t2.firstname, t2.address,";
   $query = $query . " t2.city, t2.state, t2.zip FROM";
   $query = $query . " orders as t1, customers as t2 WHERE t1.orderid = $orderid AND";
   $query = $query . " t1.custid = t2.custid";
   $result = mysql_query($query);
   $row = mysql_fetch_array($result, MYSQL_ASSOC);
   $custid = $row['custid'];
   $date = $row['date'];
   $firstname = $row['firstname'];
   $lastname = $row['lastname'];
   $address = $row['address'];
   $city = $row['city'];
   $state = $row['state'];
   $zip = $row['zip'];
   echo "<h2>Order information for order #" . $orderid . "</h2><br>\n";
   echo $firstname . " " . $lastname . "<br>\n";
   echo $address . "<br>\n";
   echo $city . ", " . $state . "  " . $zip . "<br><br>\n";
   
   echo "<h3>Items:</h3>\n";
   echo "<table width=\"75%\" cellpadding=\"1\" border=\"1\">\n";
   echo "<tr><td>Product ID</td><td>Description</td><td>Price</td><td>Quantity</td><td>Total</td></tr>\n";
   $query = "SELECT t1.prodid, t1.quantity, t2.description, t2.price";
   $query = $query . " FROM order_items as t1, products as t2 WHERE t1.orderid = $orderid";
   $query = $query . " AND t1.prodid = t2.prodid";
   $result = mysql_query($query);
   $total = 0;
   while($row = mysql_fetch_array($result, MYSQL_ASSOC))
   {
      $prodid = $row['prodid'];
      $quantity = $row['quantity'];
      $description = $row['description'];
      $price = $row['price'];
      $subtotal = $price * $quantity;
      $total += $subtotal;
      echo "<tr><td>$prodid</td><td>$description</td>\n";
      printf("<td>%.2f</td><td>%d</td><td>%.2f</td></tr>\n", $price, $quantity, $subtotal);
   }
   echo "<tr><td colspan=\"4\"><b>Order Total</b></td>\n";
   printf("<td>%.2f</td></tr>\n", $total);
   echo "</table>\n";
   echo "<form action=\"admin.php\" method=\"post\">\n";
   echo "<input type=\"hidden\" name=\"content\" value=\"postorder\">\n";
   echo "<input type=\"hidden\" name=\"orderid\" value=\"$orderid\">\n";
   echo "<input type=\"submit\" name=\"button\" value=\"Post order\">\n";
   echo "</form>\n";
?>

3. Save the file, and exit the editor.

This code builds two SQL queries, one to display the customer information for the order and another to retrieve the product information for the order. It also does some math, totaling the cost for each item and producing a total for the order.

When the manager selects the Post order button, the code passes control over to the postorder.inc.php file. In this file, you can generate a printed report or an e-mail message to send to someone to process the order. In our simple example, we'll just mark the order record as being processed and leave it at that. Let's build our simple version of the postorder.inc.php file.

1. Create a file called postorder.inc.php in the admin folder under the store folder in your application area.
2. Open the file in a text editor, and add the following code:

<?php
   $orderid = $_POST['orderid'];
   $query = "UPDATE orders SET status = 'shipped' WHERE orderid = $orderid";
   $result = mysql_query($query) or die(mysql_error());
   
   if ($result)
   {
      echo "<h2>Order processed.</h2>\n";
   } else
   {
      echo "<h2>Unable to process order at this time.</h2>\n";
   }
   echo "<a href=\"admin.php?content=process\">Process more orders</a>\n";
?>

3. Save the file, and exit the text editor.

Obviously, if this were a real store you'd want to post the order information in some sort of tracking system. For our project, though, just a simple message that the order was processed suffices.

That's all there is to that report. In Chapter 4, you'll see how to generate a completely different type of report using your PHP skills.

Creating Special Reports

In the real world, not all data resides in Web pages. There are times when you need to generate reports in a special format, such as exporting data into the accountants' favorite tool—a spreadsheet. You do this by using a little bit of HTTP trickery along with your PHP code.

Another feature in this report you'll need to use a date to filter out data in the database tables. Unfortunately, MySQL uses a somewhat odd way of storing date information. Usually it's a good idea to allow your customers to enter dates in a format they're used to using, then convert the information into what MySQL wants. Let's look at doing this first.

Working With Dates

SQL allows you to use dates in queries. This provides an easy way to extract information based on dates. MySQL's peculiar format for storing dates is

YYYY-MM-DD

where YYYY is the four-digit year, MM is the two-digit month, and DD is the two-digit day. Almost everyone else in the world displays dates differently. The trick is to create an HTML form that offers the date elements individually, and then combine those elements into the format required by MySQL. This lets your manager enter a date in a format such as 10/25/2007, then convert that into the standard 2007-10-25 MySQL format.

The report.inc.php file provides an HTML form for entering a start and end date to generate a report. We'll use those dates later to create a spreadsheet report for the manager. First, let's create the HTML form. Just follow these steps:

1. Create a file called report.inc.php in the admin folder under the store folder in your application area.
2. Open the file in a text editor, and add the following code:

<h2>Calculate Report for period: </h2>
<form action="displayreport.php" method="post">
<input type="hidden" name="content" value="quarterly">
<table width="100%" cellpadding="1" border="1">
<tr><td>Enter start date (mm/dd/yyyy):</td>
<td><input type="text" name="startmonth" size="2">
<input type="text" name="startday" size="2">
<input type="text" name="startyear" size="4"</td></tr>
<tr><td>Enter end date (mm/dd/yyyy):</td>
<td><input type="text" name="endmonth" size="2">
<input type="text" name="endday" size="2">
<input type="text" name="endyear" size="4"</td></tr>
</table>
<input type="submit" name="button" value="Get report">
</form>

3. Save the file, and exit the text editor.

There's nothing too fancy here. The code uses individual textboxes to allow the manager to enter the day, month, and year values individually and in whatever format they're accustom to (the order shown is in U.S. format; if you prefer European format, you can easily reverse the month and day textboxes).

This code passes the start and end date information to a separate program called displayreport.php. There's a reason why it does this instead of using admin.php.

Creating Spreadsheets

By default, the client's browser attempts to display all data within the browser window. The HTTP protocol provides a special header that identifies data types. If the browser recognizes a data type that an external application can handle, it has the ability to redirect the data automatically to that application.

The HTTP header specifies the data type using the Content-type label. The label that identifies an Excel spreadsheet looks like this:

The first part of the content type defines the general MIME media type of the data. The second part of the content type defines the specific media type. When the client browser sees this header, it knows that the Excel spreadsheet application is to use the data.

You can exploit that feature by using PHP to create a special HTTP header for your data, identifying the data as a spreadsheet. If you have a spreadsheet application on your PC that can read Excel spreadsheets, this HTTP header will start your application automatically.

This technique uses our friend the header() function to create the special HTTP header. If you remember, the header() function must be the first item sent to the client's browser. Because of this, we need to create a separate PHP program instead of using an include file in the admin.php program.

Let's build the code first and then walk through it.

1. Create a file called displayreport.php in the admin folder under the store folder in your application area.
2. Open the file with a text editor, and add the following code:

Print code


<?php
   header("Content-type: application/vnd.ms-excel");
   header("Content-Disposition: attachment; filename=report.xls");
   header("Pragma: no-cache");
   header("Expires: 0");
   include("/mylibrary/login.php");
   login();
   $startday = $_REQUEST['startday'];
   $startmonth = $_REQUEST['startmonth'];
   $startyear = $_REQUEST['startyear'];
   $dbstartdate = $startyear . "-" . $startmonth . "-" . $startday;
   $startdate = $startmonth . "/" . $startday . "/" . $startyear;
   $endday = $_REQUEST['endday'];
   $endmonth = $_REQUEST['endmonth'];
   $endyear = $_REQUEST['endyear'];
   $dbenddate = $endyear . "-" . $endmonth . "-" . $endday;
   $enddate = $endmonth . "/" . $endday . "/" . $endyear;

   $query = "SELECT products.description, sum(order_items.quantity) as total, products.price ";
   $query .= " FROM orders, order_items, products";
   $query .= " WHERE orders.orderid = order_items.orderid";
   $query .= " AND order_items.prodid = products.prodid";
   $query .= " AND orders.status = 'shipped'";
   $query .= " AND orders.date >= '$dbstartdate' AND orders.date <= '$dbenddate'";
   $query .= " GROUP BY products.description";
   $result = mysql_query($query);
   echo "<table width=\"100%\" cellpadding=\"1\" border=\"1\">\n";
   echo "<tr><td colspan=\"4\"><b>Products sold between " . $startdate . " and " . $enddate . "</b></td></tr>\n";
   echo "<tr><td><b>Product</b></td><td><b>Quantity Sold</b></td><td><b>Unit price</b></td><td><b>Total</b></td></tr>\n";
   $count = 3;
   while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
   {
      $product = $row['description'];
      $quantity = $row['total'];
      $price = $row['price'];
      $total = $quantity * $price;
      echo "<tr><td>$product</td><td>$quantity</td>\n";
      printf("<td>%.2f</td><td>=B%s * C%s</tr>\n", $price, $count, $count);
      $count++;
   }
   $count--;
   echo "<tr><td><b>Total</b></td><td>=SUM(B3:B" . $count . ")</td><td> </td>\n";
   echo "<td>=SUM(D3:D" . $count . ")</td></tr>\n";
   echo "</table>\n";
?>

3. Save the file, and exit the text editor.

The first few lines use the header() function to define the spreadsheet. They tell the client browser what type of file you're generating using the Content-type header, and the name of the file (make sure you use the .xls extension) using the Content-disposition header. It then uses another header to tell the client browser not to cache the data (sometimes this causes problems when using external applications).

The next section retrieves the start and end dates from the HTML form, and it creates two forms for each date: a standard form to use in the spreadsheet and a MySQL form to use in our query.

Next, the code builds the SELECT query. This is the most complicated query we've done yet in the application! Let's break it apart into pieces to discuss:

This part retrieves the product description and price from the products table, and uses the SUM() function in MySQL to add the quantities for all the occurrences of products purchased within the start and end date.

Now, the next section:

Wow, that's a mouthful! This WHERE clause looks for orders with a date greater or equal to the start date, but less than or equal to the end date. Notice that you can use standard mathematical operators with dates in MySQL! Also, it‘s looking only for orders with the status shipped, which means the manager has processed the order.

The first two items in the WHERE clause link our three data tables together, matching the linking data field between the orders and order_items tables, and the order_items and products table. This allows us to find all of the order_items records for the order and then retrieve the proper product descriptions from the products table.

Finally, the code uses another special SELECT statement clause:

Whenever you use the SUM() function, you must use the GROUP BY clause. This clause groups data records in the result set. Instead of placing the records in the result set in the order they appear in the table, MySQL will group the records together. In this example, you group the records based on the product description. With the products grouped, you can then use the SUM() function to total the quantity data fields for each product.

This is about as complicated of a query as you'll ever want to generate, but it creates an impressive report! The next (and final) step is to build the actual spreadsheet.

To build a spreadsheet, you must create an HTML table. Each cell in the table becomes a cell in the final spreadsheet. The code places the individual data records retrieved from the SQL query into the table cells where you want them to reside. The code also performs some basic calculations from the retrieved database data to enter into the spreadsheet. However, you may notice something odd with the code.

The code produces some odd text in some of the table cells. The text it produces is standard Excel spreadsheet equations. Since Excel (or another application similar to it) is processing data, you can exploit it by using the processing power of the application to do some of the work for you. By using the SUM() Excel function in the table cells, you're forcing Excel to process the data you're placing in the spreadsheet! This is a great technique to use if you're working with complex data and don't want to perform the calculations on the Web server.

Viewing the resulting spreadsheet report in Excel

That completes the code for our Food Store application. In just a few short weeks, you've completed an entire Web-based storefront application! Let's move on to Chapter 5 and wrap things up.

Summary

Today we finished the last pieces of the Food Store application. First, you learned how to use the MySQL regular expression feature to provide advanced searching capabilities easily in our Web application. Customers can enter simple expressions to search for products in the store.

Next, we discussed how to use advanced SQL queries to produce meaningful reports for the store manager. You can combine data from multiple tables into a single SQL query. This helps produce reports that include full data instead of just codes.

Finally, you discovered how to use the PHP header() function to send data to the client browser as a spreadsheet. You built the spreadsheet in your PHP code using an HTML table containing data retrieved from an SQL query, as well as Excel functions.

In the next lesson, we'll discuss a completely different way of coding using the PHP language. Object-oriented programming has taken the Web world by storm, and PHP fully supports object-oriented programming. If you want to start using object-oriented programming, it's easier to branch off from a language you already know. Also, we'll go over how to incorporate object-oriented programming easily in your PHP code.